PUSH REPORTS TO FTP

With the external command feature you can call any third party application and provide the output file as an argument. This allows you to automate the process of pushing SQL query results to an FTP server. This can be used to automatically update your website for example.

In this article we will describe how to use this to transfer files to an FTP location with WinSCP.

Step 1: Install WinSCP

You can use any FTP client, but WinSCP is a well known client which is used by many. It can be downloaded for free here:
Download WinSCP

Step 2: Create a script for WinSCP to put a file on an FTP location

When you use WinSCP to connect to your FTP server and try to upload a file, you will get a pop up window. In the left bottom corner you have the option to say ‘Generate code’.

This will generate something like:

open ftpes://user:pass@ftpserver.net/ -certificate="1f:c9:53:9e:cb:2d:bc:ac:ee:e1:cb:c6:0b:ae:82:15"
put %1%
exit

You might get a ‘put’ followed by a specific filename, change this to %1% like above. This can then later be provided with a parameter.

Save this file somewhere accessible as ftp_script.txt.

Step 3: Add an external command to AutoSQL

First set the working directory to the directory where WinSCP is installed. This is often: C:\Program Files (x86)\WinSCP

Now put the following command in the command field:

winscp.com /script=C:\Users\autosql\ftp_script.txt /parameter // {output path} 

Make sure the path of the script is pointing to the location where you saved it. The {output path} place holder will be used as the parameter we have set before in the script (the %1%). This will be filled by AutoSQL with the actual output file generated.

If you now run the action, it should show in the log that it pushed the file to your FTP location.